﻿//Description:订单导入类
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:Wangjh
//create Date:2020-4-16
//Rewriter:
//Rewrite Date:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;
using CamstarAPI;
using uMES.LeanManufacturing.DBUtility;
using System.IO;
using System.Configuration;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class BwMfgorderImportBusiness
    {
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dt"></param>
        /// <param name="apiUsername"></param>
        /// <param name="apiPassword"></param>
        /// <returns></returns>
        public  ResultModel ImportData(string filePath,ref DataTable dt,string apiUsername,string apiPassword) {
            ResultModel re = new ResultModel();
            re.IsSuccess = false;

            string type = filePath.Substring(filePath.LastIndexOf(".") + 1);

            string strconn = "";

            if (type.ToLower() == "xls")
            {
                strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            }
            else if (type.ToLower() == "xlsx")
            {
                strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
            }
            else {
                re.Message = "请选择Excel类型文件";
                return re;
            }

            //DataTable dt = new DataTable();
            using (var odbcConnection = new System.Data.OleDb.OleDbConnection(strconn)) {
                try {
                    odbcConnection.Open();

                    var strsql = "select * from [" + "Sheet1" + "$]";
                    var odbcDataAdapater = new System.Data.OleDb.OleDbDataAdapter(strsql, odbcConnection);
                    odbcDataAdapater.Fill(dt);
                    odbcConnection.Close();

                    File.Delete(filePath);
                } catch (Exception ex) {
                    File.Delete(filePath);

                    re.Message = ex.Message;
                    return re;
                }
                
            }

            dt = RemoveBlankRow(dt);

            re =TransferDtCol(dt);
            if (re.IsSuccess == false)
                return re;

            re.IsSuccess = false;
            dt.Columns.Add("Message");

            string[] judeCondition = { "ProcessNo", "OprNo", "ProductName", "ProductRev", "Qty", "UomName",
            "PlannedStartDate","PlannedCompletionDate","FinishedToFactoryName"};

            if (!judgeNullData(dt, judeCondition))//为空判断
            {
                re.Message = "请填入必填项";
                return re;
            }
            
            re = InsertMfgorderByApi(dt, apiUsername, apiPassword);

            if (re.IsSuccess)
                re.Message = "导入成功";
            else
                return re;


            return re;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
       public uMESPagingDataDTO GetSearchData(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            string lossReason =ConfigurationManager.AppSettings["CannotAgainOpenContainer"];//排除的报废原因（此种报废原因下不能继续开批次）
            string strSql =string.Format(@"select m.mfgordername,m.processno,m.oprno,pb2.productname,p.productrevision productrev,p.description productdesc,p.productid,m.qty,to_char(m.plannedstartdate,'yyyy-mm-dd') plannedstartdate,to_char(m.plannedcompletiondate,'yyyy-mm-dd') plannedcompletiondate,u.uomname,
                                    fa.factoryname FinishedToFactoryName,pt.prioritycodename PriorityName,ot.ordertypename,os.orderstatusname,m.notes,p.workflowid,
                            m.mfgorderid,m.erporderid,fa2.factoryname ReportingFactoryName,e.fullname mfgmanagerfullname,wb.workflowname,w.workflowrevision workflowrev,m.replacematerial,m.replacematerialname,m.replacematerialqty from mfgorder m
                            left join productbase pb on pb.productbaseid=m.productbaseid
                            left join product p on p.productid=nvl(pb.revofrcdid,m.productid)
                            left join productbase pb2 on pb2.productbaseid=p.productbaseid
                            left join workflow w on w.workflowid=p.workflowid
                            left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                            left join uom u on u.uomid=m.uomid 
                            left join factory fa on fa.factoryid=m.finishedtofactoryid
                            left join prioritycode pt on pt.prioritycodeid=m.priorityid
                            left join ordertype ot on ot.ordertypeid=m.ordertypeid
                            left join orderstatus os on os.orderstatusid=m.orderstatusid
                            left join factory fa2 on fa2.factoryid=m.reportingfactoryid
                            left join employee e on e.employeeid=m.mfgmanagerid
                            where 1=1 
                            AND (                            
                             SELECT NVL(SUM(c.Qty),0)+nvl(sum(scp.qty),0)  
                            FROM container c                             
                           left join scrapproductnoinfo scp on scp.containerid=c.containerid and scp.lossreasonid not in                           
                           (
                           select ls.lossreasonid  from lossreason ls where ls.lossreasonname not in ({0})
                           ) WHERE c.mfgorderid = m.mfgorderid and c.status!=0) < m.qty ", lossReason);
            if (para .ContainsKey("MfgManagerName") &&!string.IsNullOrWhiteSpace(para["MfgManagerName"]))
            {
                strSql += string.Format(" and e.employeename='{0}'", para["MfgManagerName"]);
            }
            if (para.ContainsKey("MfgManagerID") && !string.IsNullOrWhiteSpace(para["MfgManagerID"]))
            {
                strSql += string.Format(" and e.employeeid='{0}'", para["MfgManagerID"]);
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and m.ProcessNo like '%{0}%'",para["ProcessNo"]);
            }

            if (para.ContainsKey("OprNo") && !string.IsNullOrWhiteSpace(para["OprNo"]))
            {
                strSql += string.Format(" and m.OprNo like '%{0}%'", para["OprNo"]);
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strSql += string.Format(" and pb2.ProductName like '%{0}%'", para["ProductName"]);
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                strSql += string.Format(" and m.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                strSql += string.Format(" and m.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            //strSql += " order by  m.createdate DESC NULLS LAST,to_number(m.mfgordername) DESC";
            strSql += " order by  m.mfgorderid DESC,pb2.productname ASC";

            uMESPagingDataDTO result = new uMESPagingDataDTO();

            result = OracleHelper.GetPagingDataIns(strSql,int.Parse( para["CurrentPageIndex"]), int.Parse(para["PageSize"]) );
            return result;

        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSearchData2(Dictionary<string, string> para) {
            DataTable dt = new DataTable();
            string lossReason = ConfigurationManager.AppSettings["CannotAgainOpenContainer"];//排除的报废原因（此种报废原因下不能继续开批次）
            string strSql =string.Format(@"select m.mfgordername,m.processno,m.oprno,pb2.productname,p.productrevision productrev,p.description productdesc,p.productid,m.qty,to_char(m.plannedstartdate,'yyyy-mm-dd') plannedstartdate,to_char(m.plannedcompletiondate,'yyyy-mm-dd') plannedcompletiondate,u.uomname,
                                    fa.factoryname FinishedToFactoryName,pt.prioritycodename PriorityName,ot.ordertypename,os.orderstatusname,m.notes,p.workflowid,
                            m.mfgorderid,m.erporderid,fa2.factoryname ReportingFactoryName,e.fullname mfgmanagerfullname,wb.workflowname,w.workflowrevision workflowrev,m.replacematerial,m.replacematerialname,m.replacematerialqty from mfgorder m
                            left join productbase pb on pb.productbaseid=m.productbaseid
                            left join product p on p.productid=nvl(pb.revofrcdid,m.productid)
                            left join productbase pb2 on pb2.productbaseid=p.productbaseid
                            left join workflow w on w.workflowid=p.workflowid
                            left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                            left join uom u on u.uomid=m.uomid 
                            left join factory fa on fa.factoryid=m.finishedtofactoryid
                            left join prioritycode pt on pt.prioritycodeid=m.priorityid
                            left join ordertype ot on ot.ordertypeid=m.ordertypeid
                            left join orderstatus os on os.orderstatusid=m.orderstatusid
                            left join factory fa2 on fa2.factoryid=m.reportingfactoryid
                            left join employee e on e.employeeid=m.mfgmanagerid
                            where 1=1 
                             AND (                            
                             SELECT NVL(SUM(c.Qty),0)+nvl(sum(scp.qty),0)  
                            FROM container c                             
                           left join scrapproductnoinfo scp on scp.containerid=c.containerid and scp.lossreasonid not in                           
                           (
                           select ls.lossreasonid  from lossreason ls where ls.lossreasonname not in ({0})
                           ) WHERE c.mfgorderid = m.mfgorderid and c.status!=0) < m.qty ", lossReason);
            if (para.ContainsKey("MfgManagerName") && !string.IsNullOrWhiteSpace(para["MfgManagerName"]))
            {
                strSql += string.Format(" and e.employeename='{0}'", para["MfgManagerName"]);
            }
            if (para.ContainsKey("MfgManagerID") && !string.IsNullOrWhiteSpace(para["MfgManagerID"]))
            {
                strSql += string.Format(" and e.employeeid='{0}'", para["MfgManagerID"]);
            }
            if (para.ContainsKey("ProcessNo") && !string.IsNullOrWhiteSpace(para["ProcessNo"]))
            {
                strSql += string.Format(" and m.ProcessNo like '%{0}%'", para["ProcessNo"]);
            }

            if (para.ContainsKey("OprNo") && !string.IsNullOrWhiteSpace(para["OprNo"]))
            {
                strSql += string.Format(" and m.OprNo like '%{0}%'", para["OprNo"]);
            }

            if (para.ContainsKey("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                strSql += string.Format(" and pb2.ProductName like '%{0}%'", para["ProductName"]);
            }

            if (para.ContainsKey("ProductName2") && !string.IsNullOrWhiteSpace(para["ProductName2"]))
            {
                strSql += string.Format(" and pb2.ProductName ='{0}'", para["ProductName2"]);
            }

            if (para.ContainsKey("ProductID") && !string.IsNullOrWhiteSpace(para["ProductID"]))
            {
                strSql += string.Format(" and p.productid='{0}'", para["ProductID"]);
            }

            if (para.ContainsKey("PlannedStartDate1") && !string.IsNullOrWhiteSpace(para["PlannedStartDate1"]))
            {
                strSql += string.Format(" and m.plannedstartdate>=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate1"]);
            }

            if (para.ContainsKey("PlannedStartDate2") && !string.IsNullOrWhiteSpace(para["PlannedStartDate2"]))
            {
                strSql += string.Format(" and m.plannedstartdate<=to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["PlannedStartDate2"]);
            }

            //strSql += " order by  m.createdate DESC NULLS LAST,to_number(m.mfgordername) DESC";
            strSql += " order by  m.createdate DESC NULLS LAST,m.mfgorderid DESC";
                      

            dt = OracleHelper.Query(strSql).Tables[0];

            return dt;
        }

        /// <summary>
        /// 去除空白行
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        DataTable RemoveBlankRow(DataTable dt)
        {
            DataTable dt2 = dt.Clone();

            foreach (DataRow dr in dt.Rows)
            {
                bool isImport = false;
                  
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!dr.IsNull(i))
                    {
                        isImport = true;
                        break;
                    }
                }
                if (isImport)
                    dt2.ImportRow(dr);
            }

            return dt2;
        }

        /// <summary>
        /// 列名转换
        /// </summary>
        /// <param name="dt"></param>
        ResultModel TransferDtCol(DataTable dt) {

            ResultModel re = new ResultModel();
            re.IsSuccess = false;

            int num = 0;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                num++;
                if (dt.Columns[i].ColumnName == "工作令号")
                {
                    dt.Columns[i].ColumnName = "ProcessNo";
                }
                else if (dt.Columns[i].ColumnName == "作业令号")
                {
                    dt.Columns[i].ColumnName = "OprNo";
                }
                else if (dt.Columns[i].ColumnName == "图号")
                {
                    dt.Columns[i].ColumnName = "ProductName";
                }
                else if (dt.Columns[i].ColumnName == "版本")
                {
                    dt.Columns[i].ColumnName = "ProductRev";
                }
                else if (dt.Columns[i].ColumnName == "名称")
                {
                    dt.Columns[i].ColumnName = "ProductDesc";
                }
                else if (dt.Columns[i].ColumnName == "数量")
                {
                    dt.Columns[i].ColumnName = "Qty";
                }
                else if (dt.Columns[i].ColumnName == "计量单位")
                {
                    dt.Columns[i].ColumnName = "UomName";
                }
                else if (dt.Columns[i].ColumnName == "计划开始时间")
                {
                    dt.Columns[i].ColumnName = "PlannedStartDate";
                }
                else if (dt.Columns[i].ColumnName == "计划完成时间")
                {
                    dt.Columns[i].ColumnName = "PlannedCompletionDate";
                }
                else if (dt.Columns[i].ColumnName == "最后交往")
                {
                    dt.Columns[i].ColumnName = "FinishedToFactoryName";
                }
                else if (dt.Columns[i].ColumnName == "优先级")
                {
                    dt.Columns[i].ColumnName = "PriorityName";
                }
                else if (dt.Columns[i].ColumnName == "订单类型")
                {
                    dt.Columns[i].ColumnName = "OrderTypeName";
                }
                else if (dt.Columns[i].ColumnName == "订单状态")
                {
                    dt.Columns[i].ColumnName = "OrderStatusName";
                }
                else if (dt.Columns[i].ColumnName == "备注")
                {
                    dt.Columns[i].ColumnName = "Notes";
                }               
                else {
                    num--;
                }

            }

            if (num != 14)
            {
                re.Message = "导入模板列数不对";
                return re;
            }

            re.IsSuccess = true;
            return re;
        }

        /// <summary>
        /// 为空判断
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="judeCondition"></param>
        /// <returns></returns>
        bool judgeNullData(DataTable dt, string[] judeCondition ) {
            
            string sqlCondition = "";
            foreach (string str in judeCondition)
            {
                sqlCondition += str + " is null or ";
            }
            sqlCondition = sqlCondition.Remove(sqlCondition.LastIndexOf("or"));

            var selectRows = dt.Select(sqlCondition);

            if (selectRows.Length > 0)
            {
                foreach (DataRow dr in selectRows)
                {
                    dr["Message"] = "请填入必填项";
                }
                return false;
            }

            return true;

        }

        /// <summary>
        /// 重复判断
        /// </summary>
        /// <returns></returns>
        bool judgeRepeatData(DataTable dt, string[] judeCondition) {
            var view = dt.DefaultView;
            var dt1 = view.ToTable(true, judeCondition);
            var dt2 = view.ToTable(false, judeCondition);
            bool result=false;

            if (dt1.Rows.Count == dt2.Rows.Count) //无重复条数
            {
                result = true;
                return result;
            }

            return result;

        }

        /// <summary>
        /// 调用api存入数据
        /// </summary>
        /// <param name="apiUsername"></param>
        /// <param name="apiPassword"></param>
        /// <returns></returns>
      public   ResultModel InsertMfgorderByApi(DataTable dt, string apiUsername, string apiPassword) {
            ResultModel re = new ResultModel();
            re.IsSuccess = false;
            
            string mfgName = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("D2");

            string strSql = string.Format ("select max(m.mfgordername) maxname from mfgorder m where m.mfgordername like '{0}%'", mfgName);

            string maxname = OracleHelper.Query(strSql).Tables[0].Rows[0]["maxname"].ToString();
            maxname = maxname.Replace(mfgName, "");

            int no = 0;
            if (string.IsNullOrWhiteSpace(maxname))
            {
               
            }
            else
            {
                if (maxname.Length != 10)
                {
                    re.Message = "系统内订单编号错误";
                    return re;
                }
               
                if (!int.TryParse(maxname,out no))
                {
                    re.Message = "系统内订单编号错误";
                    return re;
                }
               
            }      


            Dictionary<string, List<ClientAPIEntity>> p_ChildEntity = new Dictionary<string, List<ClientAPIEntity>>();
            List<ClientAPIEntity> childEntityList = new List<ClientAPIEntity>();
            ClientAPIEntity dataEntity = new ClientAPIEntity();

            for(int i=0;i<dt.Rows.Count;i++)
            {
                no++;
                childEntityList = new List<ClientAPIEntity>();

                dataEntity = new ClientAPIEntity("Name", DataTypeEnum.DataField, mfgName+no.ToString().PadLeft(10,'0'), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("OrderType", DataTypeEnum.NamedObjectField, dt.Rows[i]["OrderTypeName"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("ProcessNo", DataTypeEnum.DataField, dt.Rows[i]["ProcessNo"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("OprNo", DataTypeEnum.DataField, dt.Rows[i]["OprNo"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("Product", DataTypeEnum.RevisionedObjectField , dt.Rows[i]["ProductName"].ToString(), dt.Rows[i]["ProductRev"].ToString());
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("Qty", DataTypeEnum.DataField, dt.Rows[i]["Qty"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("UOM", DataTypeEnum.NamedObjectField , dt.Rows[i]["UomName"].ToString(), "");
                childEntityList.Add(dataEntity);

                DateTime tempDate;

                if (DateTime.TryParse(dt.Rows[i]["PlannedStartDate"].ToString(), out tempDate))
                {
                    dataEntity = new ClientAPIEntity("PlannedStartDate", DataTypeEnum.DataField, tempDate.Date.ToString("yyyy-MM-ddTHH:mm:ss"), "");//.ToString("yyyy-MM-ddTHH:mm:ss")
                    childEntityList.Add(dataEntity);
                }
                else {
                    re.Message = "日期格式错误";
                    return re;
                }

                if (DateTime.TryParse(dt.Rows[i]["PlannedCompletionDate"].ToString(), out tempDate))
                {
                    dataEntity = new ClientAPIEntity("PlannedCompletionDate", DataTypeEnum.DataField, tempDate.Date.ToString("yyyy-MM-ddTHH:mm:ss"), "");//.ToString("yyyy-MM-ddTHH:mm:ss")
                    childEntityList.Add(dataEntity);
                }
                else
                {
                    re.Message = "日期格式错误";
                    return re;
                }
                
                dataEntity = new ClientAPIEntity("FinishedToFactory", DataTypeEnum.NamedObjectField, dt.Rows[i]["FinishedToFactoryName"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("Priority", DataTypeEnum.NamedObjectField, dt.Rows[i]["PriorityName"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("OrderStatus", DataTypeEnum.NamedObjectField, dt.Rows[i]["OrderStatusName"].ToString(), "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("Notes", DataTypeEnum.DataField, dt.Rows[i]["Notes"].ToString(), "");
                childEntityList.Add(dataEntity);

                if (dt.Columns.Contains("ERPOrderID"))
                {
                    dataEntity = new ClientAPIEntity("ERPOrderID", DataTypeEnum.DataField, dt.Rows[i]["ERPOrderID"].ToString(), "");
                    childEntityList.Add(dataEntity);
                }
                if (dt.Columns.Contains("ReportingFactoryName"))
                {
                    dataEntity = new ClientAPIEntity("ReportingFactory", DataTypeEnum.NamedObjectField, dt.Rows[i]["ReportingFactoryName"].ToString(), "");
                    childEntityList.Add(dataEntity);
                }

                dataEntity = new ClientAPIEntity("MfgManager", DataTypeEnum.NamedObjectField, apiUsername, "");
                childEntityList.Add(dataEntity);

                dataEntity = new ClientAPIEntity("CreateDate", DataTypeEnum.DataField, System.DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss"), "");//.ToString("yyyy-MM-ddTHH:mm:ss")
                childEntityList.Add(dataEntity);

                p_ChildEntity.Add(i.ToString(), childEntityList);
            }

            var api = new CamstarClientAPI(apiUsername, apiPassword);
            string strMsg = "";
            re.IsSuccess = api.RunTxnServiceHasSubentity("BwImportMfgorderDoc", "BwImportMfgorder", new List<ClientAPIEntity>(), "MfgorderInfos", p_ChildEntity, ref strMsg);
            
            re.Message = strMsg;
            return re;
        }
    }
}
